# -*- coding: utf-8 -*-
'''
###########################################
#  Created on 2011-10-25
#  @author: cl.lam
#  Description:
###########################################
'''
from datetime import datetime as dt
from threading import Lock
from itertools import chain

from sqlalchemy import Column
from sqlalchemy.types import Integer, Text, DateTime, Numeric, Date
from sqlalchemy.schema import ForeignKey
from sqlalchemy.ext.declarative.api import declared_attr
from sqlalchemy.orm import relation, backref, synonym
from sqlalchemy.sql.expression import and_, func
from sqlalchemy.orm.exc import NoResultFound
from flask.helpers import url_for

from sys2do.constant import TNS_NEW, RGN_NEW, PO_NEW, IVTNT_NEW, FIN_NEW, FIN_IN, \
    FIN_OUT, SO_NEW, IVTNT_APPROVE, IVTNT_IN, IVTLT_VIRTUAL_LIST, ITEM_LOCKED, \
    ITEM_AVAILABLE, ACTIVE
from sys2do.util.sa_helper import genNo, nextVal
from sys2do.model import DeclarativeBase, qry, db, Product, Shop, ShopProfile, InventoryLocation, \
                Supplier, Paytype, Payterm, Member, Currency, FinAccount, Item
from interface import SysMixin, DBMixin, ProductMixin
from sys2do.util.logic_helper import getPermission
from sys2do.util.exception import NoInventoryExp, MoreThanStockExp
from sys2do.util.decorator import trycatch
from sqlalchemy.ext.hybrid import hybrid_property






__all__ = ['SO', 'SODtl', 'PO', 'PODtl', 'DN', 'DNDtl', 'RGN', 'RGNDtl',
           'InventoryNote', 'InventoryNoteDtl', 'InventoryNoteDtlItem',
           'InventoryProduct',
           'FinNote', 'FinNoteDtl', 'ItemBalance',
           'StockRpt',
           ]


INVENTORY_LOCK = Lock()


class SO( DeclarativeBase, SysMixin, DBMixin ):

    __tablename__ = 'logic_sale_order'

    id = Column( Integer, autoincrement = True, primary_key = True )
#     no = Column( 'no', Text, default = genNo( 'zb_so_seq', '200', 7 ) )
    no = Column( 'no', Text, doc = u'系统编号' )
    shopID = Column( 'shop_id', Integer, ForeignKey( 'master_shop.id' ), doc = u'店铺' )
    shop = relation( Shop )

    paytermID = Column( 'payterm_id', Integer, ForeignKey( 'master_payterm.id' ), doc = u'支付方式' )
    payterm = relation( Payterm )

    memberID = Column( 'member_id', Integer, ForeignKey( 'master_member.id' ), doc = u'会员' )
    member = relation( Member, backref = 'sos' )

    currencyID = Column( 'currency_id', Integer, ForeignKey( 'master_currency.id' ), doc = u'货币' )
    currency = relation( Currency )
    ratio = Column( 'ratio', Numeric( 15, 2 ), default = 0, doc = u'汇率' )

    amount = Column( 'amount', Numeric( 15, 2 ), default = 0, doc = u'总价(元)' )
    realAmount = Column( 'real_amount', Numeric( 15, 2 ), default = 0, doc = u'折后价(元)' )
    payAmount = Column( 'pay_amount', Numeric( 15, 2 ), default = 0, doc = u'实付(元)' )
    refundAmount = Column( 'refund_amount', Numeric( 15, 2 ), default = 0, doc = u'找零(元)' )

    status = Column( 'status', Integer, default = SO_NEW, doc = u'' )
    usage = Column( 'usage', Text, doc = u'' )

    soldTime = Column( 'sold_time', DateTime, default = dt.now, doc = u'销售时间' )
    soldPerson = Column( 'sold_person', Text, doc = u'经办人' )

    def __str__( self ): return self.no
    def __repr__( self ): return self.no
    def __unicode__( self ): return self.no

    def __init__( self, *args, **kwargs ):
        super( self.__class__, self ).__init__( *args, **kwargs )
        from system import SysDict
        d = qry( SysDict ).filter( and_( SysDict.type == 'OBJECT_PREFIX', SysDict.name == self.__class__.__name__ ) ).one()
        seq = nextVal( 'zb_so_seq' )
        self.no = '%s%s%.7d' % ( d.value, dt.now().strftime( "%y%m%d" ), seq )

    def approvable( self ): return self.status == SO_NEW and getPermission( 'SALE_APPROVE' )
    def editable( self ): return self.status == SO_NEW and getPermission( 'SALE_EDIT' )
    def deletable( self ): return self.status == SO_NEW and getPermission( 'SALE_DEL' )

    def viewURL( self ): return url_for( 'bpSale.view', action = 'view', id = self.id )



class SODtl( DeclarativeBase, SysMixin, DBMixin, ProductMixin ):

    __tablename__ = 'logic_sale_order_dtl'

    id = Column( Integer, autoincrement = True, primary_key = True )
    hdrID = Column( 'hdr_id', Integer, ForeignKey( 'logic_sale_order.id' ), )
    hdr = relation( SO, backref = backref( "dtls", order_by = id ), primaryjoin = "and_(SO.id == SODtl.hdrID, SODtl.active == 0)" )

    pdtID = Column( 'pdt_id', Integer, ForeignKey( 'master_product.id' ), )
    pdt = relation( Product )

    price = Column( 'price', Numeric( 15, 2 ) )
    priceDiscount = Column( 'price_discount', Numeric( 15, 2 ) )
    realPrice = Column( 'real_price', Numeric( 15, 2 ) )

    fee = Column( 'fee', Numeric( 15, 2 ) )
    feeDiscount = Column( 'fee_discount', Numeric( 15, 2 ) )
    realFee = Column( 'real_fee', Numeric( 15, 2 ) )

    amount = Column( 'amount', Numeric( 15, 2 ) )
    realAmount = Column( 'real_amount', Numeric( 15, 2 ) )

    ivtQtys = Column( 'ivt_qtys', Text )
    qty = Column( 'qty', Integer )

    @property
    def shopIvtQtyObjs( self ):
        ivtQtyDict = {}
        for i in self.ivtQtys.split( '|' ):
            ( ivtID, qty ) = i.split( ',' )[0:2]
            itemNOs = i.split( ',' )[2:] if len( i.split( ',' ) ) > 2 else None
            ivtQtyDict[ivtID] = {
                'qty': int( qty ) if qty else 0,
                'nos': itemNOs,
            }
        results = []
        for i in self.pdt.shopIvtPdts:
            ivt = i.ivt
            ivtQty = ivtQtyDict.get( str( ivt.id ), {} )
            if i.availableQty > 0 and ivt.name not in IVTLT_VIRTUAL_LIST:
                results.append( {'pdtID': i.pdtID, 'ivtID': ivt.id,
                    'name': ivt.fullPath, 'availableQty': i.availableQty,
                    'futureQty' : i.futureQty,
                    'qty': ivtQty.get( 'qty', 0 ), 'itemNOs': ivtQty.get( 'nos', [] )
                } )
        return results

    @property
    def ivtQtyObjs( self ):
        results = []
        for i in self.ivtQtys.split( '|' ):
            ( ivtID, qty ) = i.split( ',' )[0:2]
            itemNOs = i.split( ',' )[2:] if len( i.split( ',' ) ) > 2 else None
            ivt = InventoryLocation.get( ivtID )
            results.append( {
                'ivtID': ivt.id,
                'name': ivt.fullPath,
                'qty': int( qty ) if qty else 0,
                'itemNOs': itemNOs,
            } )
        return results


class PO( DeclarativeBase, SysMixin, DBMixin ):

    __tablename__ = 'logic_purchase_order'

    id = Column( Integer, autoincrement = True, primary_key = True )
#     no = Column( 'no', Text, default = genNo( 'zb_po_seq', '300', 7 ) )
    no = Column( 'no', Text, doc = u'系统编号' )
    shopID = Column( 'shop_id', Integer, ForeignKey( 'master_shop.id' ), doc = u'店铺' )
    shop = relation( Shop )

    supplierID = Column( 'supplier_id', Integer, ForeignKey( 'master_supplier.id' ), doc = u'供应商' )
    supplier = relation( Supplier )
    att = Column( 'att', Text, doc = u'联系人' )
    tel = Column( 'tel', Text, doc = u'联系电话' )
    mobile = Column( 'mobile', Text, doc = u'手机' )

    paytypeID = Column( 'paytype_id', Integer, ForeignKey( 'master_paytype.id' ), doc = u'支付类型' )
    paytype = relation( Paytype )

    currencyID = Column( 'currency_id', Integer, ForeignKey( 'master_currency.id' ), doc = u'货币' )
    currency = relation( Currency )
    ratio = Column( 'ratio', Numeric( 15, 2 ), default = 0, doc = u'汇率' )

    amount = Column( 'amount', Numeric( 15, 2 ), default = 0, doc = u'总金额(元)' )
    realAmount = Column( 'real_amount', Numeric( 15, 2 ), default = 0, doc = u'实际总金额(元)' )
    status = Column( 'status', Integer, default = PO_NEW, doc = u'状态' )

    def __str__( self ): return self.no
    def __repr__( self ): return self.no
    def __unicode__( self ): return self.no


    def __init__( self, *args, **kwargs ):
        super( self.__class__, self ).__init__( *args, **kwargs )
        from system import SysDict
        d = qry( SysDict ).filter( and_( SysDict.type == 'OBJECT_PREFIX', SysDict.name == self.__class__.__name__ ) ).one()
        seq = nextVal( 'zb_po_seq' )
        self.no = '%s%s%.7d' % ( d.value, dt.now().strftime( "%y%m%d" ), seq )

    def editable( self ):  return self.status == PO_NEW and getPermission( 'PURCHASE_EDIT' )
    def deletable( self ): return self.status == PO_NEW and getPermission( 'PURCHASE_DEL' )
    def approvable( self ): return self.status == PO_NEW and getPermission( 'PURCHASE_APPROVE' )

    def viewURL( self ): return url_for( 'bpPrh.view', action = 'view', id = self.id )


class PODtl( DeclarativeBase, SysMixin, ProductMixin ):

    __tablename__ = 'logic_purchase_order_dtl'

    id = Column( Integer, autoincrement = True, primary_key = True )
    hdrID = Column( 'hdr_id', Integer, ForeignKey( 'logic_purchase_order.id' ), )
    hdr = relation( PO, backref = backref( "dtls", order_by = id ), primaryjoin = "and_(PO.id == PODtl.hdrID, PODtl.active == 0)" )

    pdtID = Column( 'pdt_id', Integer, ForeignKey( 'master_product.id' ), doc = u'商品' )
    pdt = relation( Product )
    qty = Column( 'qty', Integer, default = 0, doc = u'数量' )
    price = Column( 'price', Numeric( 15, 2 ), default = 0, doc = u'价格' )
    discounts = Column( 'discounts', Numeric( 15, 2 ), default = 0, doc = u'折扣' )
    realPrice = Column( 'real_price', Numeric( 15, 2 ), default = 0, doc = u'实际价格' )


class DN( DeclarativeBase, SysMixin, DBMixin ):
    __tablename__ = 'logic_deliver_note'

    id = Column( Integer, autoincrement = True, primary_key = True )
#     no = Column( 'no', Text, default = genNo( 'zb_dn_seq', '400', 7 ) )
    no = Column( 'no', Text, doc = u'系统编号' )

    sShopID = Column( 'source_shop_id', Integer, ForeignKey( 'master_shop.id' ), doc = u'源店铺' )
    sShop = relation( Shop, primaryjoin = "and_(Shop.id == DN.sShopID, DN.active == 0)" )

    dShopID = Column( 'destination_shop_id', Integer, ForeignKey( 'master_shop.id' ), doc = u'目标店铺' )
    dShop = relation( Shop, primaryjoin = "and_(Shop.id == DN.dShopID, DN.active == 0)" )


    awb = Column( 'awb', Text, doc = u'送货单号' )
    fee = Column( 'fee', Numeric( 15, 2 ), default = 0, doc = u'费用' )
    expectDate = Column( 'expect_date', Text, doc = u'预计到达日期' )
    status = Column( 'status', Integer, default = TNS_NEW, doc = u'状态' )


    def __str__( self ): return self.no
    def __repr__( self ): return self.no
    def __unicode__( self ): return self.no

    def __init__( self, *args, **kwargs ):
        super( self.__class__, self ).__init__( *args, **kwargs )
        from system import SysDict
        d = qry( SysDict ).filter( and_( SysDict.type == 'OBJECT_PREFIX', SysDict.name == self.__class__.__name__ ) ).one()
        seq = nextVal( 'zb_dn_seq' )
        self.no = '%s%s%.7d' % ( d.value, dt.now().strftime( "%y%m%d" ), seq )


    def editable( self ): return self.status == TNS_NEW and getPermission( 'DN_EDIT' )
    def deletable( self ): return self.status == TNS_NEW and getPermission( 'DN_DEL' )
    def approvable( self ): return self.status == TNS_NEW and getPermission( 'DN_APPROVE' )

    def viewURL( self ): return url_for( 'bpTns.view', action = 'view', id = self.id )


class DNDtl( DeclarativeBase, SysMixin, DBMixin, ProductMixin ):

    __tablename__ = 'logic_deliver_note_dtl'

    id = Column( Integer, autoincrement = True, primary_key = True )
    hdrID = Column( 'hdr_id', Integer, ForeignKey( 'logic_deliver_note.id' ), )
    hdr = relation( DN, backref = backref( "dtls", order_by = id ), primaryjoin = "and_(DN.id == DNDtl.hdrID, DNDtl.active == 0)" )

    pdtID = Column( 'pdt_id', Integer, ForeignKey( 'master_product.id' ), )
    pdt = relation( Product, doc = u'商品' )
    qty = Column( 'qty', Integer, default = 0, doc = u'数量' )
    ivtID = Column( 'ivt_id', Integer, doc = u'目标仓位' )

    sIvtLtnID = Column( 'source_inventory_location_id', Integer, ForeignKey( 'master_inventory_location.id' ), doc = u'源仓位' )
    sIvtLtn = relation( InventoryLocation, primaryjoin = "and_(InventoryLocation.id == DNDtl.sIvtLtnID)" )



class RGN( DeclarativeBase, SysMixin, DBMixin ):
    __tablename__ = 'logic_returngoods_note'

    id = Column( Integer, autoincrement = True, primary_key = True )
    no = Column( 'no', Text, default = genNo( 'zb_rgn_seq', '500', 7 ) )

    shopID = Column( 'source_shop_id', Integer, ForeignKey( 'master_shop.id' ), doc = u'店铺' )
    shop = relation( Shop, primaryjoin = "and_(Shop.id == RGN.shopID, RGN.active == 0)" )
    customer = Column( 'customer', Text, doc = u'客户' )
    address = Column( 'address', Text, doc = u'地址' )
    tel = Column( 'tel', Text, doc = u'联系电话' )
    mobile = Column( 'mobile', Text, doc = u'手机' )

    currencyID = Column( 'currency_id', Integer, ForeignKey( 'master_currency.id' ), doc = u'货币' )
    currency = relation( Currency )
    ratio = Column( 'amount', Numeric( 15, 2 ), default = 0, doc = u'汇率' )

    sellAmount = Column( 'sell_amount', Numeric( 15, 2 ), default = 0, doc = u'销售金额' )
    returnAmount = Column( 'return_amount', Numeric( 15, 2 ), default = 0, doc = u'退货金额' )
    status = Column( 'status', Integer, default = RGN_NEW, doc = u'状态' )

    def __str__( self ): return self.no
    def __repr__( self ): return self.no
    def __unicode__( self ): return self.no

    def editable( self ):   return getPermission( 'RGN_EDIT' )
    def deletable( self ): return getPermission( 'RGN_DEL' )


class RGNDtl( DeclarativeBase, SysMixin, DBMixin, ProductMixin ):

    __tablename__ = 'logic_returngoods_note_dtl'

    id = Column( Integer, autoincrement = True, primary_key = True )
    hdrID = Column( 'hdr_id', Integer, ForeignKey( 'logic_returngoods_note.id' ), )
    hdr = relation( RGN, backref = backref( "dtls", order_by = id ), primaryjoin = "and_(RGN.id == RGNDtl.hdrID, RGNDtl.active == 0)" )

    pdtID = Column( 'pdt_id', Integer, ForeignKey( 'master_product.id' ), doc = u'商品' )
    pdt = relation( Product )
    qty = Column( 'qty', Integer, default = 0, doc = u'数量' )
    sellPrice = Column( 'sell_price', Numeric( 15, 2 ), default = 0, doc = u'销售价格' )
    returnPrice = Column( 'return_price', Numeric( 15, 2 ), default = 0, doc = u'九月份价格' )



class InventoryNote( DeclarativeBase, SysMixin, DBMixin ):

    __tablename__ = 'logic_inventory_note'

    id = Column( Integer, autoincrement = True, primary_key = True )
#     no = Column( 'no', Text, default = genNo( 'zb_ivtnt_seq', '600', 7 ) )
    no = Column( 'no', Text, doc = u'系统编号' )

    shopID = Column( 'shop_id', Integer, ForeignKey( 'master_shop.id' ), doc = u'店铺' )
    shop = relation( Shop, backref = backref( "notes", order_by = id ), primaryjoin = "and_(Shop.id == InventoryNote.shopID, InventoryNote.active == 0)" )

    direction = Column( 'direction', Text )    # IN or OUT
    ivtID = Column( 'ivt_id', Integer, ForeignKey( 'master_inventory_location.id' ), doc = u'入库于/出库到' )    # if IN, ivtID is the source inventory , if OUT ,ivtID is destination inventory location
    ivt = relation( InventoryLocation, backref = backref( "notes", order_by = id ), primaryjoin = "and_(InventoryLocation.id == InventoryNote.ivtID, InventoryNote.active == 0)" )

    type = Column( 'type', Text )    # the reason why in or out, could be PRH,SALE,RG,TNS,
    refer = Column( 'refer', Text, doc = u'涉及' )
    status = Column( 'status', Integer, default = IVTNT_NEW, doc = u'状态' )

    appTime = Column( 'app_time', DateTime )
#     _itemList = Column( 'item_list', Text, doc = u'涉及商品' )

    def __str__( self ): return self.no
    def __repr__( self ): return self.no
    def __unicode__( self ): return self.no

    def __init__( self, *args, **kwargs ):
        super( self.__class__, self ).__init__( *args, **kwargs )
        from system import SysDict
        d = qry( SysDict ).filter( and_( SysDict.type == 'OBJECT_PREFIX', SysDict.name == self.__class__.__name__ ) ).one()
        seq = nextVal( 'zb_ivtnt_seq' )
        typePrefix = '0' if self.direction == IVTNT_IN else '1'
        self.no = '%s%s%s%.5d' % ( d.value, dt.now().strftime( "%y%m%d" ), typePrefix, seq )


    def editable( self ):
        return self.status == IVTNT_NEW and getPermission( 'INVENTORY_NOTE_EDIT' )
    def deletable( self ): return getPermission( 'INVENTORY_NOTE_DEL' )
    def approvable( self ): return self.status == IVTNT_NEW and getPermission( 'INVENTORY_NOTE_APPROVE' )
    def splitable( self ): return self.status == IVTNT_NEW and sum( map( lambda d:d.qty, self.dtls ) ) > 1


    def viewURL( self ): return url_for( 'bpIvtnt.view', action = 'view', id = self.id )

    @property
    def itemList( self ): return chain( *[d.itemList for d in self.dtls] )


    def releaseItems( self ):
        for d in self.dtls : d.releaseItems()




class InventoryNoteDtl( DeclarativeBase, SysMixin, DBMixin, ProductMixin ):

    __tablename__ = 'logic_inventory_note_dtl'

    id = Column( Integer, autoincrement = True, primary_key = True )
    hdrID = Column( 'hdr_id', Integer, ForeignKey( 'logic_inventory_note.id' ), )
    hdr = relation( InventoryNote, backref = backref( "dtls", order_by = id ), primaryjoin = "and_(InventoryNote.id == InventoryNoteDtl.hdrID, InventoryNoteDtl.active == 0)" )

    pdtID = Column( 'pdt_id', Integer, ForeignKey( 'master_product.id' ), doc = u'商品' )
    pdt = relation( Product )
    qty = Column( 'qty', Integer, default = 0, doc = u'数量' )
#     realQty = Column( 'realQty', Integer, default = 0 )

    sIvtLtnID = Column( 'source_inventory_location_id', Integer, ForeignKey( 'master_inventory_location.id' ), doc = u'源仓位' )
    sIvtLtn = relation( InventoryLocation, primaryjoin = "and_(InventoryLocation.id == InventoryNoteDtl.sIvtLtnID)" )

    dIvtLtnID = Column( 'dest_inventory_location_id', Integer, ForeignKey( 'master_inventory_location.id' ), doc = u'目标仓位' )
    dIvtLtn = relation( InventoryLocation, primaryjoin = "and_(InventoryLocation.id == InventoryNoteDtl.dIvtLtnID)" )

    _itemList = Column( 'item_list', Text, doc = u'涉及商品' )

    @trycatch( [] )
    def _getItemList( self ): return sorted( filter( bool, self._itemList.split( "|" ) ) )


    def _setItemList( self, v ):
        if not v : self._itemList = None
        elif type( v ) != list : v = [v]
        else : self._itemList = "|".join( v )


    @declared_attr
    def itemList( self ):  return synonym( '_itemList', descriptor = property( self._getItemList, self._setItemList ) )

    def releaseItems( self ):
        if not self.itemList : return
        for item in qry( Item ).filter( Item.no.in_( self.itemList ) ):
            if item.status == ITEM_LOCKED and item.refer == self.hdr.no :    # release the items locked by this note
                item.status, item.refer = ITEM_AVAILABLE, None


class InventoryNoteDtlItem( DeclarativeBase, SysMixin, DBMixin ):

    __tablename__ = 'logic_inventory_note_dtl_item'

    id = Column( Integer, autoincrement = True, primary_key = True )

    hdrID = Column( 'hdr_id', Integer, ForeignKey( 'logic_inventory_note.id' ), )
    hdr = relation( InventoryNote, backref = backref( "itemss", order_by = id ), primaryjoin = "and_(InventoryNote.id == InventoryNoteDtlItem.hdrID, InventoryNoteDtlItem.active == 0)" )

    dtlID = Column( 'dtl_id', Integer, ForeignKey( 'logic_inventory_note_dtl.id' ), )
    dtl = relation( InventoryNoteDtl, backref = backref( "itemss", order_by = id ), primaryjoin = "and_(InventoryNoteDtl.id == InventoryNoteDtlItem.dtlID, InventoryNoteDtlItem.active == 0)" )

    itemID = Column( 'item_id', Integer, ForeignKey( 'master_item.id' ), )
    item = relation( Item )



class InventoryProduct( DeclarativeBase, SysMixin, DBMixin ):

    __tablename__ = 'logic_inventory_product'

#    id = Column(Integer, autoincrement = True, primary_key = True)
    ivtID = Column( 'ivt_id', Integer, ForeignKey( 'master_inventory_location.id' ), primary_key = True, )
    ivt = relation( InventoryLocation )

    pdtID = Column( 'pdt_id', Integer, ForeignKey( 'master_product.id' ), primary_key = True, )
    pdt = relation( Product )

    qty = Column( 'qty', Integer, default = 0 )
#     inQty = Column( 'in_qty', Integer, default = 0 )
#     outQty = Column( 'out_qty', Integer, default = 0 )


    @hybrid_property
    def inQty( self ):
        return qry( func.sum( InventoryNoteDtl.qty ) ).filter( and_( 
                                           InventoryNote.active == ACTIVE, InventoryNote.status == IVTNT_NEW,
                                           InventoryNote.id == InventoryNoteDtl.hdrID,
                                           InventoryNoteDtl.dIvtLtnID == self.ivtID,
                                           InventoryNoteDtl.pdtID == self.pdtID
                                            ) ).scalar() or 0


    @hybrid_property
    def outQty( self ):
        return qry( func.sum( InventoryNoteDtl.qty ) ).filter( and_( 
                                           InventoryNote.active == ACTIVE, InventoryNote.status == IVTNT_NEW,
                                           InventoryNote.id == InventoryNoteDtl.hdrID,
                                           InventoryNoteDtl.sIvtLtnID == self.ivtID,
                                           InventoryNoteDtl.pdtID == self.pdtID
                                            ) ).scalar() or 0


    @hybrid_property
    def availableQty( self ): return self.qty - self.outQty

    @hybrid_property
    def futureQty( self ): return self.qty + self.inQty - self.outQty


    # @classmethod
    # def findInventoryBy(clz, pdtID = None, cds = []):
    #     ivtPdts = cls.findBy([clz.pdtID = pdtID])
    #     result = []
    #     for i in ivtPdts:
    #         ivt = i.ivt
    #         ivt.qty = i.qty
    #         result.append(ivt)
    #     return result

    @classmethod
    def findItemsBy( clz, shopID = None, shopProfileID = None, inventoryLocationID = None, cds = [] ):
        if shopID:
            shopProfileID = Shop.get( shopID ).getProfile().id
            # print 'shopProfileID: %s' % shopProfileID
        if shopProfileID:
            inventoryLocationID = ShopProfile.get( shopProfileID ).inventory.id
            # print 'inventoryLocationID: %s' % inventoryLocationID
        if inventoryLocationID:
            ivt = InventoryLocation.get( inventoryLocationID )
            ivtIDs = [i.id for i in ivt.getTree( includeSelf = True )]
            cds.append( Item.ivtID.in_( ivtIDs ) )
            return Item.findBy( cds )
        else:
            return None

    @classmethod
    def findProductsBy( clz, shopID = None, shopProfileID = None, inventoryLocationID = None, cds = [] ):
        if shopID:
            shopProfileID = Shop.get( shopID ).getProfile().id
            # print 'shopProfileID: %s' % shopProfileID
        if shopProfileID:
            inventoryLocationID = ShopProfile.get( shopProfileID ).inventory.id
            # print 'inventoryLocationID: %s' % inventoryLocationID
        if inventoryLocationID:
            ivt = InventoryLocation.get( inventoryLocationID )
            # print 'ivt: %s' % ivt
            # print 'ivtIDS: %s' % [i.id for i in ivt.getTree(includeSelf=True)]
            ivtPds = clz.all( [clz.ivtID.in_( [i.id for i in ivt.getTree( includeSelf = True )] )] )
            # print 'ivtPds: %s' % ivtPds
            cds.append( Product.id.in_( [i.pdtID for i in ivtPds] ) )
            return Product.findBy( cds )
        else:
            return None


    @classmethod
    def addOrUpdate( clz, ivtID, pdtID, qty, inQty, outQty ):
        '''
        qty, inQty, outQty = int( qty ), int( inQty ), int( outQty )
        if qty == 0 and inQty == 0 and outQty == 0: return None
        '''
        if qty == 0 : return None

        INVENTORY_LOCK.acquire()    # lock the below logic
        try:
            try:
                obj = qry( clz ).filter( and_( clz.active == 0, clz.ivtID == ivtID, clz.pdtID == pdtID ) ).with_lockmode( 'update' ).one()
            except NoResultFound:    # no inventory found
                if qty < 0 : raise NoInventoryExp()    # if minus the qty on a don't exist inventory, raise error
                '''
                obj = clz.create( {'ivtID' : ivtID , 'pdtID' : pdtID, 'qty' : 0 , 'inQty' : 0, 'outQty' : 0} )
                '''
                obj = clz.create( {'ivtID' : ivtID , 'pdtID' : pdtID, 'qty' : 0} )
                db.add( obj )

            if qty < 0 and obj.qty + qty < 0 : raise MoreThanStockExp()    # if the inventory qty is not enough
            obj.qty += qty
            '''
            obj.inQty += inQty
            obj.outQty += outQty
            '''
            return obj
        except Exception as e:
            raise e
        finally:
            INVENTORY_LOCK.release()    # release the lock


    #===========================================================================
    # going to move the product from source inventory to destination inventory
    # whtih qty . This method DON'T change the inventory's real qty ,it just show
    # the intention to move the product. This method could be used in DN,SO etc.
    # @param soureIvtID: the source inventory ID
    # @param destIvtId:  the destination inventory ID
    # @param pdtID: the prduct will be moved
    # @param qty: the qty to be moved
    #===========================================================================
    @classmethod
    def goingToMove( cls, soureIvtID, destIvtId, pdtID, qty ):
        '''
        cls.addOrUpdate( soureIvtID, pdtID, 0, 0, qty )
        cls.addOrUpdate( destIvtId, pdtID, 0, qty, 0 )
        '''

    #===========================================================================
    # To move the product from source inventory to destination inventory
    # whtih qty . This method change the inventory's real qty .
    # This method could be used in invenotry note approval.
    # @param soureIvtID: the source inventory ID
    # @param destIvtId:  the destination inventory ID
    # @param pdtID: the prduct will be moved
    # @param qty: the qty to be moved
    #===========================================================================
    @classmethod
    def toMove( cls, soureIvtID, destIvtId, pdtID, qty ):
        '''
        cls.addOrUpdate( soureIvtID, pdtID, qty * -1, 0, qty * -1 )
        cls.addOrUpdate( destIvtId, pdtID, qty, qty * -1, 0 )
        '''
        cls.addOrUpdate( soureIvtID, pdtID, qty * -1, 0, 0 )
        cls.addOrUpdate( destIvtId, pdtID, qty, 0, 0 )



class FinNote( DeclarativeBase, SysMixin, DBMixin ):

    __tablename__ = 'logic_fin_note'

    id = Column( Integer, autoincrement = True, primary_key = True )
#     no = Column( 'no', Text, default = genNo( 'zb_fin_note_seq', '700', 7 ) )
    no = Column( 'no', Text, doc = u'系统编号' )
    invoiceNo = Column( 'invoice_no', Text, doc = u'发票号码' )
    issueFrom = Column( 'issue_from', Text, doc = u'开票方' )
    issueTo = Column( 'issue_to', Text, doc = u'发票抬头' )

    shopID = Column( 'shop_id', Integer, ForeignKey( 'master_shop.id' ), )
    shop = relation( Shop, primaryjoin = "and_(Shop.id == FinNote.shopID, FinNote.active == 0)" )
    accountID = Column( 'account_id', Integer, ForeignKey( 'master_fin_account.id' ), doc = u'账户' )
    account = relation( FinAccount )

    refer = Column( 'refer', Text, doc = u'涉及' )
    direction = Column( 'direction', Text )    # IN or OUT

    currencyID = Column( 'currency_id', Integer, ForeignKey( 'master_currency.id' ), doc = u'货币' )
    currency = relation( Currency )
    ratio = Column( 'ratio', Numeric( 15, 2 ), default = 0, doc = u'汇率' )

    amount = Column( 'amount', Numeric( 15, 2 ), default = 0, doc = u'总金额' )
    status = Column( 'status', Integer, default = FIN_NEW, doc = u'状态' )

    def __str__( self ): return self.no
    def __repr__( self ): return self.no
    def __unicode__( self ): return self.no

    def __init__( self, *args, **kwargs ):
        super( self.__class__, self ).__init__( *args, **kwargs )
        from system import SysDict
        d = qry( SysDict ).filter( and_( SysDict.type == 'OBJECT_PREFIX', SysDict.name == self.__class__.__name__ ) ).one()
        seq = nextVal( 'zb_fin_note_seq' )
        typePrefix = '0' if self.direction == FIN_IN else '1'
        self.no = '%s%s%s%.5d' % ( d.value, dt.now().strftime( "%y%m%d" ), typePrefix, seq )

    def editable( self ):
        if self.status != FIN_NEW: return False
        if self.direction == FIN_IN and not getPermission( 'FIN_IN_EDIT' ) : return False
        if self.direction == FIN_OUT and not getPermission( 'FIN_OUT_EDIT' ) : return False
        return True

    def deletable( self ):
        if self.status != FIN_NEW: return False
        if self.direction == FIN_IN and not getPermission( 'FIN_IN_DEL' ) : return False
        if self.direction == FIN_OUT and not getPermission( 'FIN_OUT_DEL' ) : return False
        return True


    def approvable( self ):
        if self.status != FIN_NEW: return False
        if self.direction == FIN_IN and not getPermission( 'FIN_IN_APPROVE' ) : return False
        if self.direction == FIN_OUT and not getPermission( 'FIN_OUT_APPROVE' ) : return False
        return True


class FinNoteDtl( DeclarativeBase, SysMixin, DBMixin ):

    __tablename__ = 'logic_fin_note_dtl'

    id = Column( Integer, autoincrement = True, primary_key = True )

    hdrID = Column( 'hdr_id', Integer, ForeignKey( 'logic_fin_note.id' ), )
    hdr = relation( FinNote, backref = backref( "dtls", order_by = id ), primaryjoin = "and_(FinNote.id == FinNoteDtl.hdrID, FinNoteDtl.active == 0)" )

    itemName = Column( 'item_name', Text, doc = u'项目名称' )
    desc = Column( 'desc', Text, doc = u'描述' )
    refer = Column( 'refer', Text, doc = u'涉及' )
    price = Column( 'price', Numeric( 15, 2 ), default = 0, doc = u'价格' )
    qty = Column( 'qty', Integer, default = 0, doc = u'数量' )
    amount = Column( 'amount', Numeric( 15, 2 ), default = 0, doc = u'金额' )




class ItemBalance( DeclarativeBase, SysMixin, DBMixin ):

    __tablename__ = 'logic_item_balance'

    id = Column( Integer, autoincrement = True, primary_key = True )

    itemID = Column( 'item_id', Integer, ForeignKey( 'master_item.id' ), )
    item = relation( Item )

    pPrice = Column( 'pprice', Numeric( 15, 2 ), default = None, doc = u'采购价格' )
    pRefer = Column( 'prefer', Text, doc = u'采购涉及' )
    sPrice = Column( 'sprice', Numeric( 15, 2 ), default = None, doc = u'销售价格' )
    sRefer = Column( 'srefer', Text, doc = u'销售涉及' )




class StockRpt( DeclarativeBase ):

    __tablename__ = 'logic_stock_rpt'

    rptDate = Column( 'rpt_date', Date, primary_key = True, )
    ivtID = Column( 'ivt_id', Integer, ForeignKey( 'master_inventory_location.id' ), primary_key = True, )
    ivt = relation( InventoryLocation )

    pdtID = Column( 'pdt_id', Integer, ForeignKey( 'master_product.id' ), primary_key = True, )
    pdt = relation( Product )

    qty = Column( 'qty', Integer, default = 0 )
    inQty = Column( 'in_qty', Integer, default = 0 )
    outQty = Column( 'out_qty', Integer, default = 0 )

    @hybrid_property
    def availableQty( self ): return self.qty - self.outQty

    @hybrid_property
    def futureQty( self ): return self.qty + self.inQty - self.outQty
